package com.dudsbury.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import com.mysql.jdbc.PreparedStatement;

public class DatabaseCustomer extends Database {
	

	
public static MyCustomer[] getCustomers() {
		
		
		String[][] result = returnQuery("select * from Customer");
		
		MyCustomer customers[] = new MyCustomer[result[0].length];
		String data[] = new String[result.length];
		for (int y=0;y<result[0].length;y++) {
			for (int x=0;x<result.length;x++) {
				data[x] = result[x][y];
			}
			customers[y] = new MyCustomer(data);
			System.out.println(customers[y].getCustomerID());
		}
		
		return customers;
	}

public static MyUser[] getUsers() {
	Database.connect();
	//first pair of brackets for column, second one for rows
	String[][] result = returnQuery("select * from User");
	//printQuery(result);
	//if you put result.lenght you are checking the columns
	//if you put result[].lenght you are checking the rows
	MyUser users[] = new MyUser[result[0].length];
	String data[] = new String[result.length];
	for (int y=0;y<result[0].length;y++) {
		for (int x=0;x<result.length;x++) {
			data[x] = result[x][y];
		}
		users[y] = new MyUser(data);
		//System.out.println(users[y].getUserID());
	}
	Database.close();
	return users;
}



public static void saveCustomer(MyCustomer customer) {
		
		if (customer.getCustomerID()!=null) {
		customer.setCustomerID(customer.getCustomerID().replaceAll("\n", "").trim());
		}
		else {
			System.out.println("NULL");
		}
		Database.connect();
		boolean alreadyExists = false;
		System.out.println("This is the customer ID: " + customer.getCustomerID());
		MyCustomer customers[] = getCustomers();
		String id = "";
		for (MyCustomer c: customers) {
			System.out.println("This are the customer id in the database" + c);
			c.setCustomerID(c.getCustomerID().replaceAll("\n", "").trim());
			if 	((c.getCustomerID().equals(customer.getCustomerID()))) {
				alreadyExists = true;
				id = c.getCustomerID();
				System.out.println("This is the customer ID: " + id);
			} 
		}
		
		id = id.replaceAll("\n", "").trim();
	
		if (alreadyExists == true) {
						
			String update = "update Customer set title='"+customer.getTitleType() +"',Forename='"+customer.getForename()+"',Surname='"+customer.getSurname()+"',Postcode='"+customer.getPostcode()+"',Address1='"+customer.getAddress1()+"',Address2='"+customer.getAddress2()+"',Town_or_city='"+customer.getTownOrCity()+"',County='"+customer.getCounty()+"',TelephoneNo='"+customer.getTelephoneNo()+"',Email='"+customer.getEmail()+"',InfoReference='"+customer.getInfoReferenceType()+"'where CustomerID='"+id+"'";
							
			System.out.println("This is an update " + update);
			doSQL(update);
		System.out.println("Amending Customer");
		}
		else {
						
			String insert = "insert into Customer values (null,'" + customer.getTitleType() +"','" + customer.getForename() +"','" + customer.getSurname() +"','" + customer.getPostcode() +"','" + customer.getAddress1() +"','" + customer.getAddress2() +"','" + customer.getTownOrCity() +"','" + customer.getCounty() +"','" + customer.getTelephoneNo() +"','" + customer.getEmail() +"','" + customer.getTitleType() +"')";
			System.out.println("This is an insert" + insert);
			doSQL(insert);
			System.out.println("Creating Customer");
			
		}
	}

public  static ArrayList<String> customer() throws SQLException{
//Create an array list to be filled with group names
ArrayList<String> groupNames = new ArrayList<String>();
String query = "SELECT forename FROM customer"; 


Statement stmt = null;
ResultSet rs = stmt.executeQuery(query); 

while (rs.next()) { 
 String groupName = rs.getString("forename"); 
 // add group names to the array list
 groupNames.add(groupName);

} 

rs.close(); 
return groupNames;
}

public static boolean saveUser(MyUser user) {
	
	if (user.getUserID()!=null) {
		user.setUserID(user.getUserID().replaceAll("\n", "").trim());
		}
	
	boolean alreadyExists = false;

	MyUser users[] = getUsers();
	String id = "";
	for (MyUser i : users) {
		i.setUserID(i.getUserID().replaceAll("\n", "").trim());
		if (i.getUserName().equals(user.getUserName())) {
			alreadyExists = true;
			id = i.getUserID();
		}
	}
	
	id = id.replaceAll("\n", "").trim();
	
	if(alreadyExists == false){
		Database.connect();
		doSQL(String
				.format("insert into User (userId,UserName,UserPassword) values (null,'%s','%s');",
						user.getUserName(), user.getUserPassword()));
		System.out.println("Creating User");
	
	Database.close();
	}
	return alreadyExists;
}

}
